





ALTER      function fn_load_all_open_packingslips (@sales_order_no varchar(50),
			 @customer_po varchar(50), @customer_id int)
returns  @fn_results Table (CustomerShipping_Id int, SalesOrder_Id int, Label varchar(50),
			SalesOrder_No varchar(50), C_Name varchar(65), 
			SO_Customer_PO_Number varchar(50), CST_Status varchar(50),
			Inv_No varchar(50))
as 
begin
	declare @quantity_shipped int
	declare @req_quantity int
	declare @line_item_id int
	declare @customer_name varchar(65)
	declare @temp_sales_order_no varchar(50)
	declare @temp_sales_order_id int
	declare @temp_customer_id int
	declare @temp_customer_po varchar(50)
	
	declare @results Table (CustomerShipping_Id int, SalesOrder_Id int, Label varchar(50),
			SalesOrder_No varchar(50), C_Name varchar(65), 
			SO_Customer_PO_Number varchar(50), CST_Status varchar(50),
			Inv_No varchar(50))
	
	if(@sales_order_no <> '')
		declare itemCursor CURSOR FOR
			select Sales_Order.SalesOrder_Id,SalesOrder_No,LineItem_ID,COOL_Qty,Customer_ID,SO_Customer_PO_Number
			FROM Sales_Order, Customer_Open_Order_LineItem WHERE
			SalesOrder_No = @sales_order_no  and
			Sales_Order.SalesOrder_ID = Customer_Open_Order_LineItem.SalesOrder_ID and COOL_Status = 'APPROVED'
	else if(@customer_po <> '')
		declare itemCursor CURSOR FOR
			select Sales_Order.SalesOrder_Id,SalesOrder_No,LineItem_ID,COOL_Qty,Customer_ID,SO_Customer_PO_Number
			FROM Sales_Order, Customer_Open_Order_LineItem WHERE
			SO_Customer_PO_Number = @customer_po  and
			Sales_Order.SalesOrder_ID = Customer_Open_Order_LineItem.SalesOrder_ID and COOL_Status = 'APPROVED'			
	else if(@customer_id <> 0)
		declare itemCursor CURSOR FOR
			select Sales_Order.SalesOrder_Id,SalesOrder_No,LineItem_ID,COOL_Qty,Customer_ID,SO_Customer_PO_Number
			FROM Sales_Order, Customer_Open_Order_LineItem WHERE
			Customer_ID = @customer_id  and
			Sales_Order.SalesOrder_ID = Customer_Open_Order_LineItem.SalesOrder_ID and COOL_Status = 'APPROVED'
	else 
		declare itemCursor CURSOR FOR
			select Sales_Order.SalesOrder_Id,SalesOrder_No,LineItem_ID,COOL_Qty,Customer_ID,SO_Customer_PO_Number
			FROM Sales_Order, Customer_Open_Order_LineItem WHERE
			Sales_Order.SalesOrder_ID = Customer_Open_Order_LineItem.SalesOrder_ID and COOL_Status = 'APPROVED'		

	open itemCursor 
	fetch next from itemCursor into @temp_sales_order_id,@temp_sales_order_no,@line_item_id,@req_quantity,@temp_customer_id,@temp_customer_po

	while @@fetch_status = 0
	begin	
		select @quantity_shipped = 0
		select @customer_name = C_Name FROM Customer WHERE Customer_ID = @temp_customer_id
		select @quantity_shipped = SUM(CSLIT_Qty_Shipped) FROM Customer_Shipping_Line_Item_Table Where LineItem_ID = @line_Item_id and  CSLIT_Status !='CANCELLED' group by LineItem_ID
		if((@quantity_shipped is null) or (@quantity_shipped < @req_quantity))
		begin
		  INSERT INTO @results VALUES (-1,@temp_sales_order_id,null,@temp_sales_order_no,
			@customer_name,@temp_customer_po,'OPEN','')
		end	
		fetch next from itemCursor into @temp_sales_order_id,@temp_sales_order_no,@line_item_id,@req_quantity,@temp_customer_id,@temp_customer_po
	end
	close itemCursor
	deallocate itemCursor

	insert @fn_results select *from @results
	return
end



